Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Introduction
Foreword
About the Author

Part I—Introduction
Chapter 1—Introduction to Oracle
The Database
The Physical Layer
The Logical Layer
The Oracle Instance
The Oracle Memory Structure
System Global Area (SGA)
Program Global Area (PGA)
Processes
How Transactions Work
Oracle Products
Oracle RDBMS Products
Oracle Workgroup Server
Personal Oracle for Windows
Oracle Development Tools
Oracle Applications
Oracle Services
Summary
Chapter 2—Understanding Terms
Terms
RDBMS Functionality
Checkpoint
Logging and Archiving
Business Models
OnLine Transaction Processing (OLTP)
Batch Processing
Decision Support
Data Warehousing
Binary Large Objects (BLOBs)
Unit Conversions
Powers of 10
Storage Units
Summary
Chapter 3—What Is a Well-Tuned System?
Client/Server Computing
The Client or Front-End Machines
The Server
The Network
Client/Server Checklist
Host-Based Computing
The Front-End Application
The Database
Terminal-Based Checklist
Batch Computing
Batch-Processing Checklist
Exceptions
Multimedia Systems
Shipping Systems
Summary
Chapter 4—Tuning Methodology
Goals
Throughput
Response Time
Connectivity
Fault Tolerance
Load Time
Tuning Methodology
Examine the Problem
Determine the Problem
Determine the Solution and Set Goals
Test the Solution
Analyze the Results
Summary
Chapter 5—Benchmarking
Introduction to Benchmarking
Industry Standard Benchmarks
The Transaction Processing Performance Council (TPC)
TPC Rules and Regulations
Results
Benchmarks
Publication Benchmarks
Custom Benchmarks
Writing Your Own Benchmark
Summary
Chapter 6—Performance Monitoring Tools
Oracle Tools
SQL*DBA Monitor
Server Manager
Oracle SNMP Agents
SQL Trace
EXPLAIN PLAN
OS Tools
Third-Party Tools
Real-Time Monitors
Threshold Monitors
Summary
Chapter 7—Performance Engineering Starts at the Design Stage
Design Stage
Database Layout
Indexes and Clusters
Application Design
Hardware Sizing
Network Considerations
Performance Tuning after the System Is Built
Tuning the Client
Tuning the Server
Tuning the Network
Summary

Part II—Tuning the Server
Chapter 8—What Affects Oracle Server Performance?
System Bottlenecks
Finding the Bottleneck
Removing the Bottleneck
System Tuning
Tuning RDBMS Resources
Tuning OS Resources
Tuning Hardware Resources
Other Tuning Factors
System Limitations
Summary
Chapter 9—Oracle Instance Tuning
Tuning Memory
Tuning the Operating System
Tuning the Private SQL and PL/SQL Areas
Tuning the Shared Pool
Tuning the Buffer Cache
Tuning the I/O Subsystem
Understanding Disk Contention
Identifying Disk Contention Problems
Solving Disk Contention Problems
Reducing Unnecessary I/O Overhead
Migrated and Chained Rows
Dynamic Extensions
PCTFREE and PCTUSED Command Options
A Review of I/O Reduction Techniques
Tuning Rollback Segments
Understanding How Rollback Segments Work
Tuning Rollback Segments
Review of Rollback Segment Tuning
Checking for Latch Contention
Redo Log Buffer Contention
Redo Log Buffer Latch Contention
Tuning Checkpoints
Optimizing Archiving
Adjusting the Effect of Archiving
Optimizing Sorts
Minimizing Free List Contention
Summary
Chapter 10—Performance Enhancements
Block Size
Clusters
Direct-Write Sorts
Fragmentation
Hash Clusters
When To Hash
Indexes
Index Types
How the Oracle Index Works
What To Index
Multiblock Reads
Multiblock Writes
Parallel Query Option
Parallel Query Processing
Direct-Write Sorts
Parallel Index Creation
Parallel Loading
Parallel Recovery
Parallel Server Option
Spin Counts
Summary
Chapter 11—Tuning the Server Operating System
Goals
Processes
Memory
I/O
Direct or Synchronous I/O
Asynchronous I/O
Miscellaneous
Post-Wait Semaphore
Scheduling and Preemption
Cache Affinity
Summary
Chapter 12—Operating System-Specific Tuning
NetWare
Architectural Overview
Tuning Considerations
Windows NT
Architectural Overview
Tuning Considerations
OS/2
Architectural Overview
Tuning Considerations
UNIX
Architectural Overview
Tuning Considerations
Summary
Chapter 13—System Processors
Overview of Computer Architecture
CPU and Cache
CPU Design
CISC Processors
RISC Processors
Multiprocessor Systems
SMP Systems
MPP Systems
CPU Cache
System Memory Architecture
Virtual Memory System
Bus Design
Summary
Chapter 14—Advanced Disk I/O Concepts
Disk Operation
Seek Time
Rotational Latency
Data Transfer Rate
Queue Time
Disk Performance
Random I/Os
Sequential I/Os
Summary
Chapter 15—Disk Arrays
How Does a Disk Array Work?
Software Array
Hardware Array
RAID Technology
RAID-0
RAID-1
RAID-2
RAID-3
RAID-4
RAID-5
Fault-Tolerance Concerns
No Data Protection
Full Data Protection
Partial Data Protection
Configuring RAID for RDBMS Performance
Isolate Sequential I/Os
Distribute Random I/Os
Size the Volume Properly
Configure for the Disk Array
RAID Comparison
Summary

Part III—Configuring the System
Chapter 16—OLTP System
Characteristics of the OLTP System
Data Access Patterns
System Load
Goals
Design Considerations
Physical Data Layout
Hardware Considerations
Tuning Considerations
Oracle Tuning
Server OS Tuning
Enhancements
Oracle Parallel Server Option
Hardware Enhancements
Performance Verification
What To Test in the RDBMS
What To Test in the OS
Benchmarks
Summary
Chapter 17—Batch Processing System
Characteristics of the Batch Processing System
Data Access Patterns
System Load
Goals
Design Considerations
Physical Data Layout
Hardware Considerations
Tuning Considerations
Oracle Tuning
Server OS Tuning
Enhancements
Parallel Query Option
Oracle Parallel Server Option
Hardware Enhancements
Performance Verification
What To Test in the RDBMS
What To Test in the OS
Benchmarks
Summary
Chapter 18—Decision Support System
Characteristics of a DSS System
Data Access Patterns
System Load
Goals
Design Considerations
Physical Data Layout
Hardware Considerations
Tuning Considerations
Oracle Tuning
Server OS Tuning
Enhancements
Parallel Query Option
Oracle Parallel Server Option
Hardware Enhancements
Performance Verification
What To Test in the RDBMS
What To Test in the OS
Benchmarks
Summary
Chapter 19—Data Warehousing System
Characteristics of a Data Warehouse
Data Access Patterns
System Load
Goals
Design Considerations
Physical Data Layout
Fault Tolerance Consideration
Hardware Considerations
Tuning Considerations
Oracle Tuning
Server OS Tuning
Enhancements
Parallel Query Option
Oracle Parallel Server
Hardware Enhancements
Performance Verification
What To Test in the RDBMS
What To Test in the OS
Benchmarks
Summary
Chapter 20—BLOB System
Characteristics of BLOBs
Data Access Patterns
System Load
Goals
Design Considerations
Physical Data Layout
Hardware Considerations
Tuning Considerations
Oracle Tuning
Server OS Tuning
Enhancements
Hardware Enhancements
Performance Verification
What To Test in the RDBMS
What To Test in the OS
Benchmarks
Summary
Chapter 21—The Oracle Parallel Server System
Oracle Parallel Server Architecture
Design Considerations
Design Goals
System Design
Tuning the Parallel Server System
Summary
Chapter 22—Optimal Backup and Recovery
RDBMS Operational Review
Backup Process
Recovery Process
Characteristics of the Oracle Backup Process
Cold (Offline) Backup
Hot (Online) Backup
Data Access Patterns During Backup
System Load During Backup
Backup Goals
System Design Considerations
Cold Database Backup
Hot Database Backup
Tuning Considerations
System Enhancements To Improve Backup Performance
CPU Enhancements
I/O Enhancements
Network Enhancements
Split Up the Backup
Performance Verification
What To Test in the RDBMS
What To Test in the OS
Summary
Chapter 23—Miscellaneous Configurations
Financial Systems
System Characteristics
Design and Tuning Hints
Enhancements
Replicated Systems
System Characteristics
Design and Tuning Hints
Distributed Systems
System Characteristics
Design and Tuning Hints
TextServer 3.0 Systems
System Characteristics
Design and Tuning Hints
Enhancements
Oracle Office Systems
System Characteristics
Design and Tuning Hints
WebServer Systems
System Characteristics
Design and Tuning Hints
Enhancements
Summary

Part IV—Tuning SQL
Chapter 24—What Is a Well-Tuned SQL Statement?
How To Identify Badly Formed SQL Statements
Transaction Processing
SQL Statement Processing
Cursor Creation
Statement Parsing
Query Processing
Bind Variables
Statement Execution
Parallelization
Fetch Rows To Be Returned
Summary
Chapter 25—Using EXPLAIN PLAN and SQL Trace
SQL Trace
SQL Trace Initialization
Controlling SQL Trace
SQL Trace Functionality
TKPROF Functionality
Interpreting SQL Trace
The EXPLAIN PLAN Command
EXPLAIN PLAN Initialization
Invoking EXPLAIN PLAN
Extracting EXPLAIN PLAN Results
Registering Applications
Summary
Chapter 26—Tuning SQL Statements
Tuning an Existing Application
Problem Analysis
Tuning the Application
Designing a New Application
Indexes
Clusters
Hash Clusters
Packages, Procedures, and Functions
Optimization Approaches
Discrete Transactions
Summary
Chapter 27—Using the Oracle Optimizer
How the Optimizer Works
How To Specify an Optimization Mode
Optimization Methods
Rule-Based Approach
Cost-Based Approach
Using the ANALYZE Command
How To Run the ANALYZE Command
Data Dictionary Statistics
Hints
Summary
Chapter 28—Using Procedures, Functions, and Packages
Review of the Library Cache
Procedures and Functions
Procedures
Functions
How Procedures and Functions Operate
How To Create Stored Procedures and Stored Functions
How To Replace Procedures and Functions
Packages
Summary
Chapter 29—Providing for Data Integrity and Triggers
Integrity Constraints
Referential Integrity
Integrity Constraints
Using Constraints
Triggers
Using Triggers
Using Alerts
Creating Triggers
Viewing Triggers
Audit Trails
Serial Reads
Summary
Chapter 30—Using Hints
Implementing Hints
Hint Syntax
Hint Errors
Using Multiple Hints
Hints
Optimization Approaches
Access Methods
Parallel Query Hints
Summary
Chapter 31—Introducing SQL Development Tools
Database Design Tools
Oracle Designer/2000
Third-Party Tools
Application Development Tools
Oracle Tools
Third-Party Tools
Analysis Tools
Oracle Mission Control
Third-Party Tools
Summary
Chapter 32—Miscellaneous SQL Topics
Table Sequences
Creating Sequences
Tuning Sequences
Using Sequences
Using Cached Sequences for Primary Key Values
Join Performance
Equijoin
Self Join
Cartesian Product
Outer Join
Tuning Joins for Throughput
Tuning Joins for Response Time
Locking
What Is Locking?
Serializable Reads
Using Locks
Array Processing
Using VARCHAR2 instead of CHAR
Summary

Part V—Tuning the Client
Chapter 33—What Affects Client Performance?
What Is a Client Machine?
The Traditional Computing Model
The Network Computing Model
The GUI/Server Model
The Client/Server Model
Two-Tiered and Three-Tiered Models
Two-Tiered System
Three-Tiered System
Client Bottlenecks
Network Performance
Application Performance
Presentation Performance
Client Hardware Performance
Summary
Chapter 34—Tuning the Client System
Windows NT
Tuning Memory
16-bit Applications
I/O Performance
Microsoft Windows 3.1 and Windows for Workgroups 3.11
Memory
Network
Microsoft Windows 95
32-Bit Support
Memory
Network
Oracle Support
UNIX
Memory
Network
Hardware
Summary
Chapter 35—Using GUI Builders
Tuning the Application
First-Generation Graphical Application Development Tools
Modern Graphical Application Development Tools
How To Test and Improve Automatically Generated SQL Statements
Oracle Tools
Developer/2000
Power Objects
Third-Party Tools
Delphi from Borland
ReportSmith
SQLWindows from Gupta
PowerBuilder from Powersoft
Summary
Chapter 36—Using Middleware Products
What Is Middleware?
Two-Tiered System Architecture
Three-Tiered System Architecture
Application Servers
How To Tune the Application Server
Transaction Monitor (TM)
What Is a TM?
When To Use a Transaction Monitor
Tuning the TM and System
Summary

Part VI—Tuning the Network
Chapter 37—What Affects Network Performance?
Network Architecture
Hardware Components
Summary
Chapter 38—Tuning the Network Components
Software Tuning
NetWare
Windows NT
OS/2
UNIX
Oracle Tuning
Network Design
Bandwidth Considerations
Segmenting the Network
Bridges, Routers, and Hubs
Summary

Part VII—References
Appendix A—Review of Tuning Guidelines
RDBMS Tuning
SGA
Performance Enhancements
OS Tuning
OS Tuning Goals
OS Features
I/O Tuning
System Design
Application Tuning
Client Tuning
Network Tuning
Appendix B
Appendix C
Appendix D
Appendix E
Appendix F
Index

Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.